/***************************************************************************
 *   Copyright (C) 2008 by Phillip Aldridge   *
 *   info@gmail.com   *
 *                                                                         *
 *   This program is free software; you can redistribute it and/or modify  *
 *   it under the terms of the GNU General Public License as published by  *
 *   the Free Software Foundation; either version 2 of the License, or     *
 *   (at your option) any later version.                                   *
 *                                                                         *
 *   This program is distributed in the hope that it will be useful,       *
 *   but WITHOUT ANY WARRANTY; without even the implied warranty of        *
 *   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the         *
 *   GNU General Public License for more details.                          *
 *                                                                         *
 *   You should have received a copy of the GNU General Public License     *
 *   along with this program; if not, write to the                         *
 *   Free Software Foundation, Inc.,                                       *
 *   59 Temple Place - Suite 330, Boston, MA  02111-1307, USA.             *
 ***************************************************************************/
#include "database.h"


#include "config.h"
#include "stationdata.h"
#include <qdatetime.h>
#include <qsqlerror.h>

/**
   Handles all datbase actions
   TODO make the table names variable or from the defines in config.h
   TODO optimize tables at a regular interval

*/
Database::Database(QWidget *parent, const char *name)
 : QWidget(parent, name)
{
   //nameBase=QString("meteo2008.");
   //nameTable=QString("weatherinfo");
   //nameDataBase=QString("meteo20082.weatherinfo");
   setRange();
}


Database::~Database()
{
}
/**
   TODO set a date range for the custom view
   
*/
void Database::setRange()
{
   QDate s;
   s = QDate::currentDate(); // This shold be a variable
   startDate =s.toString(QString("yyyy-MM-dd"));
}

void Database::setStationData(StationData *s)
{
   stationdata=s;
}

void Database::transferData()
{
  qDebug("Transferring data to station data");
  stationdata->setDate("1991-02-03");
  stationdata->setTime("12:34:01");

}
bool Database::open()
{
    defaultDB = QSqlDatabase::addDatabase( _DB_DRIVER );
    defaultDB->setDatabaseName( _DB_DBNAME );
    defaultDB->setUserName( _DB_USER );
    defaultDB->setPassword( _DB_PASSWD );
    defaultDB->setHostName( _DB_HOST );
    if ( defaultDB->open() ) {
 qDebug("Conected to database");
      return true;
    }else
    { 
 qDebug("Failed to conect to database");
      return false;
   }
}


bool Database::getLatestRecord()
{

QString sql;
      qDebug("Getting latest record from db");
      
      sql=QString("SELECT rec_date,rec_time, temp_in, temp_out, dewpoint, rel_hum_in, rel_hum_out,  windspeed,  wind_direction, wind_angle, wind_chill, rain_1h, rain_24h, rain_1w, rain_1m, rain_total, rel_pressure, abs_pressure, tendency, forecast FROM "+QString(_DB_DATATABLE)+" WHERE 1 ORDER BY timestamp DESC LIMIT 1");

      QSqlQuery query( sql );
      if ( query.next() )
      {
         query.first();
         stationdata->setDate( QString(query.value(0).toString()) );
         stationdata->setTime( QString(query.value(1).toString()) );
         stationdata->setTempIn( QString(query.value(2).toString()) );
         stationdata->setTempOut( QString(query.value(3).toString()) );
         stationdata->setDewpoint( QString(query.value(4).toString()) );
         stationdata->setRelHumIn( QString(query.value(5).toString()) );
         stationdata->setRelHumOut( QString(query.value(6).toString()) );
         stationdata->setWindSpeed( QString(query.value(7).toString()) );
         stationdata->setWindDirection( QString(query.value(8).toString()) );
         stationdata->setWindAngle( QString(query.value(9).toString()) );
         stationdata->setWindChill( QString(query.value(10).toString()) );
         stationdata->setRain1h( QString(query.value(11).toString()) );
         stationdata->setRain24h( QString(query.value(12).toString()) );
         stationdata->setRain1w( QString(query.value(13).toString()) );
         stationdata->setRain1m( QString(query.value(14).toString()) );
         stationdata->setRainTotal( QString(query.value(15).toString()) );
         stationdata->setRelPressure( QString(query.value(16).toString()) );
         stationdata->setAbsPressure( QString(query.value(17).toString()) );
         stationdata->setTendency( QString(query.value(18).toString()) );
         stationdata->setForecast( QString(query.value(19).toString()) );
      
     /*
         Get Max and Min temp for today using lastDate
      */
      QDate date = QDate::currentDate(); // This shold be a variable
      QString s;
      s=date.toString(QString("yyyy-MM-dd"));
      sql=QString("SELECT temp_out,rec_time FROM "+QString(_DB_DATATABLE)+" WHERE rec_date='%1' ORDER BY temp_out DESC LIMIT 1").arg(s);

      QSqlQuery query2( sql );
      if ( query2.next() )
      {
         query2.first();
         stationdata->setTempMax( QString(query2.value(0).toString()) );
         stationdata->setTempMaxTime( QString(query2.value(1).toString()) );
      }
      sql=QString("SELECT temp_out,rec_time FROM "+QString(_DB_DATATABLE)+" WHERE rec_date='%1' ORDER BY temp_out ASC LIMIT 1").arg(s);

      QSqlQuery query3( sql );
      if ( query3.next() )
      {
         query3.first();
         stationdata->setTempMin( QString(query3.value(0).toString()) );
         stationdata->setTempMinTime( QString(query3.value(1).toString()) );
      }
      return true;
   }else 
   {
   // failed to get record
   qDebug("Failed to get last database record");
      return false;
   }
}

QValueList <float> Database::getDayRecords(QString rec,QString table)
{
   yValues.clear ();
   if (table.isEmpty()) return yValues;
   if (rec.isEmpty()) return yValues;

  // QString rec=QString("windspeed");
   QString sql;
   QDate date = QDate::currentDate(); // This shold be a variable
   QString s;
   s=date.toString(QString("yyyy-MM-dd"));
   sql=QString("SELECT "+rec+" FROM "+QString(_DB_DBNAME)+"." + table +" WHERE rec_date='%1' ORDER BY timestamp ASC").arg(s);

   QSqlQuery query( sql );
   while ( query.next() )
   {
      QString v=query.value(0).toString();
      yValues.append( v.toFloat() );

   }
return (yValues);
}

QValueList <float> Database::get24Records(QString rec)
{

   yValues.clear ();
   if (rec.isEmpty()) return yValues;
  // QString rec=QString("windspeed");
   QString sql;
   QDate date = QDate::currentDate(); // This shold be a variable
   QString s;
   s=date.toString(QString("yyyy-MM-dd"));
   sql=QString("SELECT "+rec+" FROM "+QString(_DB_DBNAME)+".last24 WHERE 1 ORDER BY hour DESC");

   QSqlQuery query( sql );
   while ( query.next() )
   {
      QString v=query.value(0).toString();
      yValues.append( v.toFloat() );

   }
return (yValues);
}

QValueList <float> Database::getDailyRecords(QString rec,QString table,int days=31)
{
   yValues.clear ();
   if (table.isEmpty()) return yValues;
   if (rec.isEmpty()) return yValues;
   QString gs;
   QDate gd= QDate::currentDate();
   gd=gd.addDays(- days);
   gs =gd.toString(QString("yyyy-MM-dd"));
   QString sql=QString("SELECT "+rec+" FROM "+QString(_DB_DBNAME)+"." + table +" WHERE rec_date>='%1' ORDER BY rec_date ASC").arg(gs);
  //qDebug(sql);
   QSqlQuery query( sql );
   while ( query.next() )
   {
      QString v=query.value(0).toString();
      yValues.append( v.toFloat() );

   }
return (yValues);
}

/**
   archive data from the weatherinfo table
   to days table
   TODO add a timer function to call this automatically
   TODO seperate archiving task
   TODO return an error value
*/
bool Database::archiveData()
{
   archiveLast24();
   archivePreviousDay();
   archiveAllPreviousDays();
   archiveMonths();
   purgeOldData();
   return true;
}

/**
   sql script to transfer data from weatherinfo table
   to last 24h
   TODO return an error value
*/

bool Database::archiveLast24()
{
qDebug("Archiving last 24 hours");
int h;
QString sql1,sqlLast24,sqlHours;
QDateTime now= QDateTime::currentDateTime();

for (h=0;h<=23;h++)
{
   QDateTime now1= now.addSecs(-((60*60)*h));
   QString dFrom = now1.toString(QString("yyyyMMddhh0000"));
   QString dTo = now1.toString(QString("yyyyMMddhh5959"));
   QString rec_date = now1.toString(QString("yyyy-MM-dd hh:00:00"));
   sql1=QString("SELECT "
         " MAX(temp_out), MIN(temp_out),AVG(temp_out),"
         " MAX(rel_hum_out), MIN(rel_hum_out), AVG(rel_hum_out),"
         " MAX(dewpoint), MIN(dewpoint),AVG(dewpoint),"
         " MAX(rel_pressure), MIN(rel_pressure),AVG(rel_pressure),"
         " MAX(windspeed), AVG(wind_angle)"
         " FROM "+QString(_DB_DATATABLE)+" "
         " WHERE (timestamp>='%1') AND (timestamp<='%2')"
         " GROUP BY rec_date").arg(dFrom).arg(dTo);
   QSqlQuery query1( sql1 );
   while( query1.next())
   {
      sqlLast24=QString("REPLACE `last24` ("
            "`tempMax`, `tempMin`,`tempAvg`,"
            "`rel_hum_outMax`, `rel_hum_outMin`,`rel_hum_outAvg`,"
            "`dewpointMax`, `dewpointMin`, `dewpointAvg`,"
            "`rel_pressureMax`, `rel_pressureMin`,`rel_pressureAvg`,"
            "`windspeedMax`,`wind_angleAvg`,`hour`"
            ") VALUES ('%1','%2','%3'")
               .arg(query1.value(0).toString())
               .arg(query1.value(1).toString())
               .arg(query1.value(2).toString())
               ;
//
      sqlLast24 +=QString(",'%1','%2','%3'")
            .arg(query1.value(3).toString())
               .arg(query1.value(4).toString())
               .arg(query1.value(5).toString())
               ;
//
      sqlLast24 +=QString(",'%1','%2','%3'")
            .arg(query1.value(6).toString())
               .arg(query1.value(7).toString())
               .arg(query1.value(8).toString())
               ;
//
      sqlLast24 +=QString(",'%1','%2','%3'")
            .arg(query1.value(9).toString())
               .arg(query1.value(10).toString())
               .arg(query1.value(11).toString())
               ;
//
      sqlLast24 +=QString(",'%1','%2','%3'")
            .arg(query1.value(12).toString())
            .arg(query1.value(13).toString())
            .arg(h)
               ;
      sqlLast24 +=QString(")")
               ;
// update the last2H table
      QSqlQuery queryLast24(sqlLast24);
      queryLast24.next();

     sqlHours=QString("REPLACE `hours` ("
            "`tempMax`, `tempMin`,`tempAvg`,"
            "`rel_hum_outMax`, `rel_hum_outMin`,`rel_hum_outAvg`,"
            "`dewpointMax`, `dewpointMin`, `dewpointAvg`,"
            "`rel_pressureMax`, `rel_pressureMin`,`rel_pressureAvg`,"
            "`windspeedMax`,`wind_angleAvg`,`rec_date`"
            ") VALUES ('%1','%2','%3'")
               .arg(query1.value(0).toString())
               .arg(query1.value(1).toString())
               .arg(query1.value(2).toString())
               ;
//
      sqlHours +=QString(",'%1','%2','%3'")
            .arg(query1.value(3).toString())
               .arg(query1.value(4).toString())
               .arg(query1.value(5).toString())
               ;
//
      sqlHours +=QString(",'%1','%2','%3'")
            .arg(query1.value(6).toString())
               .arg(query1.value(7).toString())
               .arg(query1.value(8).toString())
               ;
//
      sqlHours +=QString(",'%1','%2','%3'")
            .arg(query1.value(9).toString())
               .arg(query1.value(10).toString())
               .arg(query1.value(11).toString())
               ;
//
      sqlHours +=QString(",'%1','%2','%3'")
            .arg(query1.value(12).toString())
            .arg(query1.value(13).toString())
            .arg(rec_date)
               ;
      sqlHours +=QString(")")
               ;
      QSqlQuery queryHours(sqlHours);
      queryHours.next();
   //qDebug(sqlHours);
    }
   }
return true;
}
/**
   Archive the previous day from weatherinfo to days
   Not sure if this is really useful ??
   TODO return an error value
*/
bool Database::archivePreviousDay()
{
qDebug("Archiving previous day");

QDateTime now= QDateTime::currentDateTime();
QDateTime now24= now.addDays(-1);
QString previousDay =now24.toString(QString("yyyy-MM-dd"));

QString sql1,sql2,sql3,sql4,sql5,sql10;

sql1=QString("SELECT "
      " MAX(temp_out), MIN(temp_out),AVG(temp_out)"
      " FROM `"+QString(_DB_DATATABLE)+"` "
      " WHERE rec_date='%1' "
      " GROUP BY rec_date").arg(previousDay);
      QSqlQuery query1( sql1 );
      query1.next();

sql2=QString("SELECT "
      " MAX(rel_hum_out), MIN(rel_hum_out), AVG(rel_hum_out)"
      " FROM `"+QString(_DB_DATATABLE)+"` "
      " WHERE rec_date='%1' "
      " GROUP BY rec_date").arg(previousDay);
      QSqlQuery query2( sql2 );
      query2.next();

sql3=QString("SELECT "
      " MAX(dewpoint), MIN(dewpoint),AVG(dewpoint)"
      " FROM `"+QString(_DB_DATATABLE)+"` "
      " WHERE rec_date='%1' "
      " GROUP BY rec_date").arg(previousDay);
      QSqlQuery query3( sql3 );
      query3.next();

sql4=QString("SELECT "
      " MAX(rel_pressure), MIN(rel_pressure),AVG(rel_pressure)"
      " FROM `"+QString(_DB_DATATABLE)+"` "
      " WHERE rec_date='%1' "
      " GROUP BY rec_date").arg(previousDay);
      QSqlQuery query4( sql4 );
      query4.next();

sql5=QString("SELECT "
      " MAX(windspeed), AVG(wind_angle),MAX(rain_24h)"
      " FROM `"+QString(_DB_DATATABLE)+"` "
      " WHERE rec_date='%1' "
      " GROUP BY rec_date").arg(previousDay);
      QSqlQuery query5( sql5 );
      query5.next();

sql10=QString("REPLACE `days` ("
      "`rec_date`,`tempMax`, `tempMin`,`tempAvg`,"
      "`rel_hum_outMax`, `rel_hum_outMin`,`rel_hum_outAvg`,"
      "`dewpointMax`, `dewpointMin`, `dewpointAvg`,"
      "`rel_pressureMax`, `rel_pressureMin`,`rel_pressureAvg`,"
      "`windspeedMax`,`wind_angleAvg`,`rainfall`"
      ") VALUES ('%1','%2','%3','%4'")
         .arg(previousDay)
         .arg(query1.value(0).toString())
         .arg(query1.value(1).toString())
         .arg(query1.value(2).toString())
         ;
sql10 +=QString(",'%1','%2','%3'")
        .arg(query2.value(0).toString())
         .arg(query2.value(1).toString())
         .arg(query2.value(2).toString())
         ;
sql10 +=QString(",'%1','%2','%3'")
        .arg(query3.value(0).toString())
         .arg(query3.value(1).toString())
         .arg(query3.value(2).toString())
         ;
sql10 +=QString(",'%1','%2','%3'")
        .arg(query4.value(0).toString())
         .arg(query4.value(1).toString())
         .arg(query4.value(2).toString())
         ;
sql10 +=QString(",'%1','%2','%3'")
        .arg(query5.value(0).toString())
         .arg(query5.value(1).toString())
         .arg(query5.value(2).toString())
         ;
sql10 +=QString(")");

         QSqlQuery query10( sql10);
         query10.next();
//qDebug(sql10);
return true;

}
/**
   Archive the previous days except yesterday
   Transfer from weatherinfo table to days table
   I think this could replace archivePreviouDays
*/
bool Database::archiveAllPreviousDays()
{
//openDatabase();
qDebug("Archiving all previous days");

QDateTime now= QDateTime::currentDateTime();
QString previousDay =now.toString(QString("yyyy-MM-dd"));

QString sql1,sql2,sql3,sql4,sql5,sql10;

sql1=QString("SELECT "
      " MAX(temp_out), MIN(temp_out),AVG(temp_out),"
      " MAX(rel_hum_out), MIN(rel_hum_out), AVG(rel_hum_out),"
      " MAX(dewpoint), MIN(dewpoint),AVG(dewpoint),"
      " MAX(rel_pressure), MIN(rel_pressure),AVG(rel_pressure),"
      " MAX(windspeed), AVG(wind_angle),Max(rain_24h),`rec_date`"
      " FROM "+QString(_DB_DATATABLE)+" "
      " WHERE rec_date<'%1' "
      " GROUP BY rec_date").arg(previousDay);

 QSqlQuery query1( sql1 );
while( query1.next())
{
   sql10=QString("REPLACE `days` ("
         "`tempMax`, `tempMin`,`tempAvg`,"
         "`rel_hum_outMax`, `rel_hum_outMin`,`rel_hum_outAvg`,"
         "`dewpointMax`, `dewpointMin`, `dewpointAvg`,"
         "`rel_pressureMax`, `rel_pressureMin`,`rel_pressureAvg`,"
         "`windspeedMax`,`wind_angleAvg`,`rainfall`,`rec_date`"
         ") VALUES ('%1','%2','%3'")
            .arg(query1.value(0).toString())
            .arg(query1.value(1).toString())
            .arg(query1.value(2).toString())
            ;
   sql10 +=QString(",'%1','%2','%3'")
            .arg(query1.value(3).toString())
            .arg(query1.value(4).toString())
            .arg(query1.value(5).toString())
            ;
   sql10 +=QString(",'%1','%2','%3'")
            .arg(query1.value(6).toString())
            .arg(query1.value(7).toString())
            .arg(query1.value(8).toString())
            ;
   sql10 +=QString(",'%1','%2','%3'")
         .  arg(query1.value(9).toString())
            .arg(query1.value(10).toString())
            .arg(query1.value(11).toString())
            ;
   sql10 +=QString(",'%1','%2','%3','%4'")
            .arg(query1.value(12).toString())
            .arg(query1.value(13).toString())
            .arg(query1.value(14).toString())
            .arg(query1.value(15).toString())
            ;
   sql10 +=QString(")");
            QSqlQuery query10( sql10);
            query10.next();
//emit message(sql10);
   }
   return true;
}
/**
   Archive data from days table to month table
*/
bool Database::archiveMonths()
{

qDebug("Archive to months");

QDate now= QDate::currentDate();
QDate m1=now.addMonths(-0);
QDate m2=now.addMonths(-1);
QString thisMonth =m1.toString(QString("yyyy-MM-01"));
QString lastMonth =m2.toString(QString("yyyy-MM-01"));
 //addMonths 
QString sql1,sql2,sql3,sql4,sql5,sql10;

sql1=QString("SELECT "
      " MAX(tempMax), MIN(tempMin), AVG(tempAvg),"
      " MAX(rel_hum_outMax), MIN(rel_hum_outMin), AVG(rel_hum_outAvg),"
      " MAX(dewpointMax), MIN(dewpointMin),AVG(dewpointAvg),"
      " MAX(rel_pressureMax), MIN(rel_pressureMin),AVG(rel_pressureAvg),"
      " MAX(windspeedMax), AVG(wind_angleAvg),SUM(rainfall)"
      " FROM `days` "
      " WHERE rec_date>='%1' AND rec_date<'%2'")
      .arg(lastMonth)
      .arg(thisMonth)
       ;

//emit message(sql1);
QSqlQuery query1( sql1 );
while( query1.next())
{
   sql10=QString("REPLACE `months` ("
      "`rec_date`,`tempMax`, `tempMin`,`tempAvg`,"
      " rel_hum_outMax, rel_hum_outMin, rel_hum_outAvg,"
      " dewpointMax, dewpointMin,dewpointAvg,"
      " rel_pressureMax,rel_pressureMin,rel_pressureAvg,"
      "`windspeedMax`,`wind_angleAvg`,`rainfall`"
      ") VALUES ('%1','%2','%3','%4'")
         .arg(thisMonth)
         .arg(query1.value(0).toString())
         .arg(query1.value(1).toString())
         .arg(query1.value(2).toString())
         ;
sql10 +=QString(",'%1','%2','%3'")
        .arg(query1.value(3).toString())
         .arg(query1.value(4).toString())
         .arg(query1.value(5).toString())
         ;
sql10 +=QString(",'%1','%2','%3'")
        .arg(query1.value(6).toString())
         .arg(query1.value(7).toString())
         .arg(query1.value(8).toString())
         ;
sql10 +=QString(",'%1','%2','%3'")
        .arg(query1.value(9).toString())
         .arg(query1.value(10).toString())
         .arg(query1.value(11).toString())
         ;
sql10 +=QString(",'%1','%2','%3'")
        .arg(query1.value(12).toString())
         .arg(query1.value(13).toString())
         .arg(query1.value(14).toString())
         ;
sql10 +=QString(")");

         QSqlQuery query10( sql10);
         query10.next();
//qDebug(sql10);

   }
   return true;
}
/**
   purges old data from weatherinfo table
   currently hard set to data from 3 months ago
   TODO automate this to be done at a certain time
*/
bool Database::purgeOldData()
{

   QDate gd= QDate::currentDate();
   gd=gd.addMonths(-3);
   QString gs =gd.toString(QString("yyyy-MM-dd"));
   QString sql=QString("DELETE FROM "+QString(_DB_DATATABLE)+" WHERE rec_date<'%1' ").arg(gs);
   QSqlQuery query( sql );
   return true;

}

//
